with tmp_network as (
    select case
               when network_type = 3 then 1 --加盟
               when network_type = 4 then 2 --中心
               when network_type = 5 then 3 --集散
               when network_type = 6 then 4 --网点
               when network_type = 2 then 5 --代理区
               when network_type = 1 then 6 --总部
               else 0
        end            as network_type
         , code
         , name        as network_name     --网点名称
         , agent_code                      --代理区编码
         , agent_name                      --代理区名称
         , fran_code   as franchisee_code  --加盟商编码
         , fran_name   as franchisee_name  --加盟商名称
         , center_code as transcenter_code --转运中心编码
         , center_name as transcenter_name --转运中心名称
    from jms_dim.dim_network_whole_massage
)


insert
overwrite
table
jms_dwd.dwd_tab_barscan_arrival_base_hi
partition
(
dt
)
select tmp_barscan.recordid              as record_id
     , tmp_barscan.billcode              as waybill_no
     , tmp_barscan.listcode              as list_code
     , tmp_barscan.subbillcode           as subbill_code
     , tmp_barscan.transfercode          as transfer_code
     , tmp_barscan.packagecode           as package_code
     , tmp_barscan.scantype              as scan_type
     , tmp_barscan.nextstation           as pre_station
     , tmp_barscan.destination           as destination
     , tmp_barscan.scanuser              as scan_user
     , tmp_barscan.inputsite             as scan_site
     , tmp_barscan.scantime              as scan_time
     , tmp_barscan.inputtime             as input_time
     , tmp_barscan.operatedate           as operate_date
     , tmp_barscan.send_deliver_user     as send_deliver_user
     , tmp_barscan.sendcustomer          as send_customer
     , tmp_barscan.sendsite              as send_site
     , tmp_barscan.destsite              as dest_site
     , tmp_barscan.transfercenter        as transfer_center
     , tmp_barscan.pcs                   as pcs
     , tmp_barscan.weight                as weight
     , tmp_barscan.goodstype             as good_type
     , tmp_barscan.expresstype           as transport_type
     , tmp_barscan.shifts                as shifts
     , tmp_barscan.transfer_deliver_fee  as transfer_deliver_fee
     , tmp_barscan.networkfee            as network_manage_fee
     , tmp_barscan.otherfee              as other_fee
     , tmp_barscan.checkflag             as check_flag
     , tmp_barscan.accountflag           as match_oper_source
     , tmp_barscan.receiptflag           as receipt_flag
     , tmp_barscan.returnflag            as return_flag
     , tmp_barscan.elescaleflag          as elescale_flag
     , tmp_barscan.unrecordflag          as weight_flag
     , tmp_barscan.writebackflag         as writeback_flag
     , tmp_barscan.lockflag              as auto_type_flag
     , tmp_barscan.remark                as width
     , tmp_barscan.modifyuser            as station_num
     , tmp_barscan.modifytime            as modify_time
     , tmp_barscan.mobile                as length
     , tmp_barscan.pistolid              as pistol_id
     , tmp_barscan.remark1               as height
     , tmp_barscan.remark2               as flight_num
     , tmp_barscan.scansitecode          as scan_site_code
     , tmp_barscan.uploadtime            as upload_time
     , tmp_barscan.remark3               as len_wid_hei_product
     , tmp_barscan.scanusercode          as scan_user_code
     , tmp_barscan.send_deliver_usercode as send_deliver_usercode
     , tmp_barscan.nextstationcode       as pre_station_code
     , tmp_barscan.source                as source
     , tmp_barscan.remark5               as remark5
     , tmp_barscan.remark6               as remark6
     , tmp_barscan.remark4               as list_code2
     , tmp_barscan.scansiteid            as scan_site_id
     , tmp_barscan.nextstationid         as pre_station_id
     , tmp_barscan.scanuserid            as scan_user_id
     , tmp_barscan.send_deliver_userid   as send_deliver_userid
     , tmp_network.network_type          as network_type
     , tmp_network.agent_code            as site_agent_code
     , tmp_network.agent_name            as site_agent_name
     , tmp_network.franchisee_code       as site_franchisee_code
     , tmp_network.franchisee_name       as site_franchisee_name
     , tmp_network.transcenter_code      as site_transcenter_code
     , tmp_network.transcenter_name      as site_transcenter_name
     , tmp_network_pre.network_type      as pre_network_type
     , case
           WHEN (LENGTH(BILLCODE) = 13) THEN 'GB'
           WHEN (LENGTH(BILLCODE) = 15 AND BILLCODE LIKE "55%") THEN 'BS'
           WHEN (LENGTH(BILLCODE) = 12 AND (BILLCODE LIKE "40%" OR BILLCODE LIKE "42%")) THEN 'BSB'
           WHEN (LENGTH(BILLCODE) = 15 AND BILLCODE LIKE "JT%") THEN 'JT'
           WHEN (LENGTH(BILLCODE) = 13 AND BILLCODE LIKE "B%") THEN 'JTB'
           else "Others"
    end                                  as waybill_source
     , tmp_barscan.dt                    as dt
from jms_ods.tab_barscan_arrival_hi tmp_barscan
         left join tmp_network tmp_network on tmp_barscan.scansitecode = tmp_network.code
         left join tmp_network tmp_network_pre on tmp_barscan.nextstationcode = tmp_network_pre.code
where tmp_barscan.dt ='{{ execution_date | cst_hour }}';